import os
import re
import requests
from io import BytesIO
import pymysql
from docx import Document
from docx.shared import Pt, Inches
from docx.oxml.ns import qn
from docx.enum.text import WD_PARAGRAPH_ALIGNMENT, WD_LINE_SPACING
from PIL import Image
from concurrent.futures import ThreadPoolExecutor, as_completed

# 使用 pymysql 连接 MySQL 数据库
db = pymysql.connect(
    host="localhost",  # 数据库地址
    user="root",  # 数据库用户名
    password="wxmp2014",  # 数据库密码
    database="fdds",  # 数据库名
    charset='utf8mb4',  # 解决中文字符问题
    cursorclass=pymysql.cursors.DictCursor  # 返回字典类型
)

cursor = db.cursor()

# 查询 books 表中的所有数据
cursor.execute("SELECT * FROM books LIMIT 1000")
books = cursor.fetchall()

# 保存文件的目录
doc_dir = "/docs/test2"  # 替换为你的项目 doc 文件夹路径

# 确保目录存在
if not os.path.exists(doc_dir):
    os.makedirs(doc_dir)


# 定义清理文件名函数
def clean_filename(filename):
    return re.sub(r'[\\/*?:"<>|]', "", filename)


# 定义设置格式的函数
def set_font(run, font_name, font_size, is_bold=False, color=None):
    run.font.name = font_name
    run._element.rPr.rFonts.set(qn('w:eastAsia'), font_name)
    run.font.size = Pt(font_size)
    run.bold = is_bold
    if color:
        run.font.color.rgb = color


def set_style(doc, name="Normal", font_name='黑体', font_size=24, is_bold=True, color=None, line_spacing=28,
              first_line_indent=2, left_indent=0):
    style = doc.styles.add_style(name, 1)  # 1 表示标题样式
    style.font.name = 'Times New Roman'
    style._element.rPr.rFonts.set(qn('w:eastAsia'), font_name)
    style.font.size = Pt(font_size)
    paragraph_format = style.paragraph_format
    paragraph_format.line_spacing = Pt(line_spacing)
    paragraph_format.line_spacing_rule = WD_LINE_SPACING.EXACTLY
    paragraph_format.first_line_indent = Pt(first_line_indent * 12)
    paragraph_format.left_indent = Pt(left_indent)


def download_image(img_url, image_path):
    response = requests.get(img_url)
    if response.status_code == 200:
        image = Image.open(BytesIO(response.content))
        image = image.convert("RGB")
        image.save(image_path, "JPEG")
    return image_path


def insert_image(doc, urlType, bookTitle, imgUrl, height_in_inches=3):
    img_folder = os.path.join(os.getcwd(), "imgs")
    if not os.path.exists(img_folder):
        os.makedirs(img_folder)

    image_name = f"{clean_filename(bookTitle)}{urlType}.jpg"
    image_path = os.path.join(img_folder, image_name)

    if os.path.exists(image_path):
        print(f"图片 {image_name} 已存在。")
    else:
        image_path = download_image(imgUrl, image_path)

    try:
        image = Image.open(image_path)
        original_width, original_height = image.size
        aspect_ratio = original_width / original_height
        target_height_in_inches = height_in_inches
        target_width_in_inches = target_height_in_inches * aspect_ratio

        p = doc.add_paragraph()
        run = p.add_run()
        run.add_picture(image_path, width=Inches(target_width_in_inches), height=Inches(target_height_in_inches))
        p.alignment = WD_PARAGRAPH_ALIGNMENT.CENTER
        p.paragraph_format.line_spacing = 1.0
    except Exception as e:
        print(f"Error inserting image: {e}")


def process_book(book):
    title = clean_filename(book['title'])
    bookCategoryName = clean_filename(book['bookCategoryName'])
    author = clean_filename(book['author'])
    speaker = clean_filename(book['speaker'])
    file_path = os.path.join(doc_dir, f"{bookCategoryName}-《{title}》-{author}-{speaker}.docx")

    if os.path.exists(file_path):
        print(f"文件 {file_path} 已存在，跳过生成。")
        return

    os.makedirs(os.path.dirname(file_path), exist_ok=True)
    doc = Document()

    set_style(doc, '标题1', '方正小标宋简体', 24, False, "", 28, 0)
    set_style(doc, '标题2', '黑体', 14, False, "", 18, 0)
    set_style(doc, '正文1', '方正仿宋_GB2312', 12, False, "", 18, 2)

    section = doc.sections[0]
    section.top_margin = Pt(105)
    section.bottom_margin = Pt(99)
    section.left_margin = Pt(79)
    section.right_margin = Pt(73.6)

    paragraph = doc.add_paragraph(title, style='标题1')
    paragraph.alignment = WD_PARAGRAPH_ALIGNMENT.CENTER

    if book['videoCoverUrl']:
        insert_image(doc, 'banner', book['title'], book['videoCoverUrl'], 3)

    doc.add_paragraph(f"作者: {book['author']}", style='标题2')
    doc.add_paragraph(f"解说: {book['speaker']}", style='标题2')
    doc.add_paragraph(f"分类: {book['bookCategoryName']}", style='标题2')
    doc.add_paragraph(f"荐语:", style='标题2')
    doc.add_paragraph(f"{book['recommendInfo']}", style='正文1')
    doc.add_paragraph(f"音频链接: \n{book['audioUrl']}", style='正文1')
    doc.add_paragraph(f"视频链接: \n{book['videoUrl']}", style='正文1')
    doc.add_paragraph(f"思维导图:", style='标题2')

    if book['mindMapUrl']:
        insert_image(doc, 'dt', book['title'], book['mindMapUrl'], 5.5)

    doc.add_paragraph(f"演说稿:", style='标题2')

    book_text_cleaned = book['bookText'].replace('&nbsp;', '\n').replace(' ', '\n')
    paragraph = doc.add_paragraph(f"\n{book_text_cleaned}", style='正文1')
    run = paragraph.runs[0]
    set_font(run, '方正仿宋_GB2312', 12)
    paragraph.paragraph_format.line_spacing_rule = WD_LINE_SPACING.EXACTLY
    paragraph.paragraph_format.line_spacing = Pt(18)

    doc.save(file_path)


# 使用线程池处理并行任务
with ThreadPoolExecutor(max_workers=5) as executor:
    futures = [executor.submit(process_book, book) for book in books]

    for future in as_completed(futures):
        try:
            future.result()
        except Exception as exc:
            print(f"生成文档时出错: {exc}")

# 关闭数据库连接
cursor.close()
db.close()
